﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace 注册登录
{
    public static class Connection
    {
        static readonly String connString = "Data Source = .\\SQL2005;Initial Catalog = study;Integrated Security=true";
        public static bool SqlNoQuery(string sql, SqlParameter[] parameters)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = connString;
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql;
            cmd.Parameters.AddRange(parameters);
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                cmd.Parameters.Clear();
                cmd.Dispose();
                conn.Close();
            }
        }

        public static DataSet SqlQuery(string sql, SqlParameter[] parameters)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = connString;
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql;
            cmd.Parameters.AddRange(parameters);
            DataSet data = new DataSet();
            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            dataAdapter.SelectCommand = cmd;
            try
            {
                dataAdapter.Fill(data, "Info");
                return data;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                cmd.Parameters.Clear();
                cmd.Dispose();
                conn.Close();
            } 
        }

        private static SqlDataAdapter GetSqlQueryAdapter(string sql, SqlParameter[] parameters)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = connString;
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql;
            cmd.Parameters.AddRange(parameters);
            DataSet data = new DataSet();
            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            dataAdapter.SelectCommand = cmd;
            return dataAdapter;
        }

        public static bool UpdateData(string sql, SqlParameter[] parameters,DataTable dataTable)
        {
            SqlDataAdapter adapter = GetSqlQueryAdapter(sql, parameters);
            SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter);
            try
            {
                adapter.Update(dataTable);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                adapter.SelectCommand.Connection.Close();
                adapter.Dispose();
            }
        }
    }
}
